package com.google.code.sms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.google.code.sms.Logger;
import com.google.code.sms.service.metadata.MetaData;

/**
 * Provides database services for video meta-data.
 *
 * @author Scott Ware
 */

public class VideoMetaDataDao extends AbstractDao {
    
    private static final Logger LOG = Logger.getLogger("VideoMetaDataDao");
    private static final String COLUMNS = "media_element_id, title, bit_rate, duration, width, height, year, cover_art_path, poster_path, subtitles_path, video_codec, audio_codec, audio_sample_rate, audio_configuration, rating, description, tagline, certificate";
    private final RowMapper rowMapper = new VideoMetaDataDao.VideoMetaDataMapper();

    /**
     * Returns the video meta-data for the given media element ID.
     *
     * @param id The media element ID.
     * @return The video meta-data or null.
     */
    public MetaData getMetaData(int id) {
        return queryOne("select " + COLUMNS + " from video_metadata where media_element_id=?", rowMapper, id);
    }
    
     /**
     * Creates or updates video meta-data.
     *
     * @param metadata The video meta-data to create/update.
     */
    public synchronized void createOrUpdate(MetaData metadata) 
    {    
        if(metadata.getMediaElementID() == null)
        {
            LOG.warn("Media Element ID not defined");
            return;
        }
        
        String sql = "update video_metadata set "
                + "title=?,"
                + "bit_rate=?,"
                + "duration=?,"
                + "width=?,"
                + "height=?,"
                + "year=?,"
                + "cover_art_path=?,"
                + "poster_path=?,"
                + "subtitles_path=?,"
                + "video_codec=?,"
                + "audio_codec=?,"
                + "audio_sample_rate=?,"
                + "audio_configuration=?,"
                + "rating=?,"
                + "description=?,"
                + "tagline=?,"
                + "certificate=?"
                + "where media_element_id=?";

        int n = update(sql, metadata.getTitle(), metadata.getBitRate(), metadata.getDuration(), metadata.getWidth(), metadata.getHeight(), metadata.getYear(), metadata.getCoverArtPath(), metadata.getPosterPath(), metadata.getSubtitlesPath(), metadata.getVideoCodec(), metadata.getAudioCodec(), metadata.getAudioSampleRate(), metadata.getAudioConfiguration(), metadata.getRating(), metadata.getDescription(), metadata.getTagline(), metadata.getCertificate(), metadata.getMediaElementID());

        if (n == 0) {

            update("insert into video_metadata (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")",
                metadata.getMediaElementID(), metadata.getTitle(), metadata.getBitRate(), metadata.getDuration(), metadata.getWidth(), metadata.getHeight(), metadata.getYear(), metadata.getCoverArtPath(), metadata.getPosterPath(), metadata.getSubtitlesPath(), metadata.getVideoCodec(), metadata.getAudioCodec(), metadata.getAudioSampleRate(), metadata.getAudioConfiguration(), metadata.getRating(), metadata.getDescription(), metadata.getTagline(), metadata.getCertificate());
        }
    }

    public void deleteMetaData(int id) {
        update("delete from video_metadata where media_element_id=?", id);
        update("checkpoint");
    }
    
    private static class VideoMetaDataMapper implements ParameterizedRowMapper<MetaData> {

        @Override
        public MetaData mapRow(ResultSet rs, int rowNum) throws SQLException 
        {
            MetaData metadata = new MetaData();
            
            metadata.setMediaElementID(rs.getInt(1));
            metadata.setTitle(rs.getString(2));
            metadata.setBitRate(rs.getInt(3) == 0 ? null : rs.getInt(3));
            metadata.setDuration(rs.getInt(4) == 0 ? null : rs.getInt(4));
            metadata.setWidth(rs.getInt(5) == 0 ? null : rs.getInt(5));
            metadata.setHeight(rs.getInt(6) == 0 ? null : rs.getInt(6));
            metadata.setYear(rs.getInt(7) == 0 ? null : rs.getInt(7));
            metadata.setCoverArtPath(rs.getString(8));
            metadata.setPosterPath(rs.getString(9));
            metadata.setSubtitlesPath(rs.getString(10));
            metadata.setVideoCodec(rs.getString(11));
            metadata.setAudioCodec(rs.getString(12));
            metadata.setAudioSampleRate(rs.getInt(13));
            metadata.setAudioConfiguration(rs.getString(14));
            metadata.setRating(rs.getFloat(15) == 0.0 ? null : rs.getFloat(15));
            metadata.setDescription(rs.getString(16));
            metadata.setTagline(rs.getString(17));
            metadata.setCertificate(rs.getString(18));
            
            return metadata;
        }
    }
}